with bussiness as (select billcode,
                          --运单号
                          scantime,
                          --扫描时间
                          scansitecode --扫描站点
                   from jms_dwd.dwd_tab_barscan_business_base_dt
                   where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                       and '{{ execution_date | cst_ds }}'
                     and scantype = '快件揽收')


insert
overwrite
table
jms_dm.dm_business_reach_details_dt
partition
(
dt
,
dtype
)
select taking_site_code,
       sign_site_code,
       subscribe_source_code,
       customer_code,
       if(
                   cainiao_platform_effect is not null
                   and is_pres_reach is not null
           ,
                   if(
                                   is_buss_reach = 0
                               and is_pres_reach = 1,
                                   1,
                                   0
                       ),
                   null
           )                           is_effect,
       is_buss_reach,
       is_pres_reach,
       waybill_no,
       customer_name,
       subscribe_source_name,
       taking_provider_id,
       taking_provider_desc,
       taking_city_id,
       taking_city_desc,
       taking_agent_code,
       taking_agent_name,
       taking_site_name,
       sign_provider_id,
       sign_provider_desc,
       sign_city_id,
       sign_city_desc,
       sign_agent_code,
       sign_agent_name,
       sign_site_name,
       buss_taking_time,
       taking_time,
       pres_sign_time,
       cainiao_platform_effect,
       buss_time_diff,
       pres_time_diff,
       taking_time_diff,
       if(taking_time_diff = 1, 1, 0)  is_taking_one_diff,
       if(taking_time_diff = 2, 1, 0)  is_taking_two_diff,
       if(taking_time_diff = 3, 1, 0)  is_taking_three_diff,
       if(taking_time_diff = 4, 1, 0)  is_taking_four_diff,
       if(taking_time_diff = 5, 1, 0)  is_taking_five_diff,
       if(taking_time_diff = 6, 1, 0)  is_taking_six_diff,
       if(taking_time_diff >= 7, 1, 0) is_taking_seven_diff,
       date(buss_taking_time)          buss_taking_day,
       date(pres_sign_time)            pres_sign_day,
       date(taking_time)               taking_day,
       date(buss_taking_time)          date_time,
       1                               detail_type,
       date(buss_taking_time)          dt,
       1                               dtype
from (select waybill_no,
             customer_code,
             customer_name,
             nvl(island_code, subscribe_source_code)                subscribe_source_code,
             nvl(island_name, subscribe_source_name)                subscribe_source_name,
             start_net.provider_id                                  taking_provider_id,
             start_net.provider_desc                                taking_provider_desc,
             start_net.city_id                                      taking_city_id,
             start_net.city_desc                                    taking_city_desc,
             start_net.agent_code                                   taking_agent_code,
             start_net.agent_name                                   taking_agent_name,
             nvl(start_pick_network_code, pick_network_code)        taking_site_code,
             start_net.name                                         taking_site_name,
             end_net.provider_id                                    sign_provider_id,
             end_net.provider_desc                                  sign_provider_desc,
             end_net.city_id                                        sign_city_id,
             end_net.city_desc                                      sign_city_desc,
             end_net.agent_code                                     sign_agent_code,
             end_net.agent_name                                     sign_agent_name,
             nvl(sign_network_code, dispatch_network_code)          sign_site_code,
             end_net.name                                           sign_site_name,
             buss_taking_time,
             taking_time,
             pres_sign_time,
             cainiao_config.platform_effect                         cainiao_platform_effect,
             datediff(date(pres_sign_time), date(buss_taking_time)) buss_time_diff,
             datediff(date(pres_sign_time), date(taking_time))      pres_time_diff,
             if(
                     cainiao_config.platform_effect is not null,
                     if(
                                 datediff(
                                         nvl(pres_sign_time, current_date()),
                                         date(buss_taking_time)
                                     ) > cainiao_config.platform_effect,
                                 0,
                                 1
                         ),
                     null
                 )                                                  is_buss_reach,
             if(
                         cainiao_config.platform_effect is not null
                         and taking_time is not null,
                         if(
                                     datediff(
                                             nvl(pres_sign_time, current_date()),
                                             date(taking_time)
                                         ) > cainiao_config.platform_effect,
                                     0,
                                     1
                             ),
                         null
                 )                                                  is_pres_reach,
             datediff(date(taking_time), date(buss_taking_time))    taking_time_diff
      from (select bussiness.billcode     as                    waybill_no,
                   bussiness.scantime     as                    buss_taking_time,
                   bussiness.scansitecode as                    buss_taking_site_code,
                   nvl(rate.customer_code, order.customer_code) customer_code,
                   nvl(rate.customer_name, order.customer_name) customer_name,
                   order.order_source_code,
                   order.order_source_name,
                   order.subscribe_source_code,
                   if(order.subscribe_source_name
                          not in
                      ('苏宁', '麦田圈', '七星潭', '有赞', '极地湾', '逍遥峰', '桃花岛', '紫金山', '桃花岛跨境',
                       '桃花岛跨境（逆）', '唯品会面单')
                       , '其他', order.subscribe_source_name)   subscribe_source_name,
                   order.pick_network_code,
                   order.dispatch_network_code,
                   rate.island_code,
                   rate.island_name,
                   rate.pres_sign_time,
                   rate.start_pick_network_code,
                   rate.sign_network_code,
                   rate.taking_time,
                   rate.cainiao_is_platform_real_standard,
                   rate.cainiao_pro_is_platform_pres_standard,
                   rate.cainiao_platform_effect,
                   rate.cainiao_platform_effect_type
            from bussiness
                     left join jms_dwd.dwd_yl_oms_oms_order_incre_dt
            order
            on bussiness.billcode =
            order.waybill_id
                and
            order.dt between date_add('{{ execution_date | cst_ds }}', -20 + 1)
                and '{{ execution_date | cst_ds }}'
                left join jms_dm.dm_prescription_reach_details_dt rate
            on bussiness.billcode = rate.waybill_no
                and rate.dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
            where nvl(rate.taking_time, '2099-12-31 23:59:59') >= bussiness.scantime) detail
               left join jms_dim.dim_network_whole_massage start_net
                         on start_net.code = nvl(start_pick_network_code, pick_network_code)
               left join jms_dim.dim_network_whole_massage end_net
                         on end_net.code = nvl(sign_network_code, dispatch_network_code)
               left join (select *
                          from jms_dim.dim_tab_platform_effect_config_base
                          where platform_name = '七星潭') cainiao_config
                         on start_net.city_desc = cainiao_config.start_city
                             and end_net.city_desc = cainiao_config.end_city
                             and date(detail.buss_taking_time) >=
                                 from_unixtime(unix_timestamp(cainiao_config.effect_start_time), 'yyyy-MM-dd')
                             and date(detail.buss_taking_time) <
                                 date_add(from_unixtime(unix_timestamp(cainiao_config.effect_end_time), 'yyyy-MM-dd'),
                                          1)) time_detail
    distribute by dt
   , dtype
   , PMOD(HASH(RAND()), 4);




insert overwrite table jms_dm.dm_business_reach_details_dt partition (dt, dtype)
select taking_site_code,
       sign_site_code,
       subscribe_source_code,
       customer_code,
       is_effect,
       is_buss_reach,
       is_pres_reach,
       waybill_no,
       customer_name,
       subscribe_source_name,
       taking_provider_id,
       taking_provider_desc,
       taking_city_id,
       taking_city_desc,
       taking_agent_code,
       taking_agent_name,
       taking_site_name,
       sign_provider_id,
       sign_provider_desc,
       sign_city_id,
       sign_city_desc,
       sign_agent_code,
       sign_agent_name,
       sign_site_name,
       buss_taking_time,
       taking_time,
       pres_sign_time,
       cainiao_platform_effect,
       buss_time_diff,
       pres_time_diff,
       taking_time_diff,
       is_taking_one_diff,
       is_taking_two_diff,
       is_taking_three_diff,
       is_taking_four_diff,
       is_taking_five_diff,
       is_taking_six_diff,
       is_taking_seven_diff,
       buss_taking_day,
       pres_sign_day,
       taking_day,
       date(taking_day) date_time,
       2                detail_type,
       date(taking_day) dt,
       2                dtype
from jms_dm.dm_business_reach_details_dt
where dt >= date_add('{{ execution_date | cst_ds }}', -22)
  and dtype = 1
  and date(taking_day) between date_add('{{ execution_date | cst_ds }}', -11 + 1)
    and '{{ execution_date | cst_ds }}'
    distribute by dt
    , dtype
    , PMOD(HASH(RAND()), 4);



insert overwrite table jms_dm.dm_business_reach_details_dt partition (dt, dtype)
select taking_site_code,
       sign_site_code,
       subscribe_source_code,
       customer_code,
       is_effect,
       is_buss_reach,
       is_pres_reach,
       waybill_no,
       customer_name,
       subscribe_source_name,
       taking_provider_id,
       taking_provider_desc,
       taking_city_id,
       taking_city_desc,
       taking_agent_code,
       taking_agent_name,
       taking_site_name,
       sign_provider_id,
       sign_provider_desc,
       sign_city_id,
       sign_city_desc,
       sign_agent_code,
       sign_agent_name,
       sign_site_name,
       buss_taking_time,
       taking_time,
       pres_sign_time,
       cainiao_platform_effect,
       buss_time_diff,
       pres_time_diff,
       taking_time_diff,
       is_taking_one_diff,
       is_taking_two_diff,
       is_taking_three_diff,
       is_taking_four_diff,
       is_taking_five_diff,
       is_taking_six_diff,
       is_taking_seven_diff,
       buss_taking_day,
       pres_sign_day,
       taking_day,
       date(pres_sign_day) date_time,
       3                   detail_type,
       date(pres_sign_day) dt,
       3                   dtype
from jms_dm.dm_business_reach_details_dt
where dt >= date_add('{{ execution_date | cst_ds }}', -22)
  and dtype = 1
  and date(pres_sign_day) between date_add('{{ execution_date | cst_ds }}', -11 + 1)
    and '{{ execution_date | cst_ds }}'
    distribute by dt
    , dtype
    , PMOD(HASH(RAND()), 4);
